/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2008                    */
/* Created on:     2013-11-16 19:00:31                          */
/*==============================================================*/


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Final_Grades') and o.name = 'FK_FINAL_GR_FINALGRAD_CREDITIN')
alter table Final_Grades
   drop constraint FK_FINAL_GR_FINALGRAD_CREDITIN
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Final_Grades') and o.name = 'FK_FINAL_GR_SUBJECTS__SUBJECTS')
alter table Final_Grades
   drop constraint FK_FINAL_GR_SUBJECTS__SUBJECTS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Final_Grades') and o.name = 'FK_FINAL_GR_USERS_FIN_USERS')
alter table Final_Grades
   drop constraint FK_FINAL_GR_USERS_FIN_USERS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Partial_Grades') and o.name = 'FK_PARTIAL__GRADES_CR_CREDITIN')
alter table Partial_Grades
   drop constraint FK_PARTIAL__GRADES_CR_CREDITIN
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Partial_Grades') and o.name = 'FK_PARTIAL__SUBJECTS__SUBJECTS')
alter table Partial_Grades
   drop constraint FK_PARTIAL__SUBJECTS__SUBJECTS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Partial_Grades') and o.name = 'FK_PARTIAL__USERS_GRA_USERS')
alter table Partial_Grades
   drop constraint FK_PARTIAL__USERS_GRA_USERS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Subjects') and o.name = 'FK_SUBJECTS_SUBJECTS__USERS')
alter table Subjects
   drop constraint FK_SUBJECTS_SUBJECTS__USERS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Users') and o.name = 'FK_USERS_GROUPS_US_GROUPS')
alter table Users
   drop constraint FK_USERS_GROUPS_US_GROUPS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Users') and o.name = 'FK_USERS_USER_ROLE_ROLES')
alter table Users
   drop constraint FK_USERS_USER_ROLE_ROLES
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('subjects_groups') and o.name = 'FK_SUBJECTS_SUBJECTS__SUBJECTS')
alter table subjects_groups
   drop constraint FK_SUBJECTS_SUBJECTS__SUBJECTS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('subjects_groups') and o.name = 'FK_SUBJECTS_SUBJECTS__GROUPS')
alter table subjects_groups
   drop constraint FK_SUBJECTS_SUBJECTS__GROUPS
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Crediting_Form')
            and   type = 'U')
   drop table Crediting_Form
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Final_Grades')
            and   name  = 'users_finalgrades_FK'
            and   indid > 0
            and   indid < 255)
   drop index Final_Grades.users_finalgrades_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Final_Grades')
            and   name  = 'finalgrades_creditingform_FK'
            and   indid > 0
            and   indid < 255)
   drop index Final_Grades.finalgrades_creditingform_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Final_Grades')
            and   name  = 'subjects_finalgrades_FK'
            and   indid > 0
            and   indid < 255)
   drop index Final_Grades.subjects_finalgrades_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Final_Grades')
            and   type = 'U')
   drop table Final_Grades
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Groups')
            and   type = 'U')
   drop table Groups
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Partial_Grades')
            and   name  = 'grades_creditingform_FK'
            and   indid > 0
            and   indid < 255)
   drop index Partial_Grades.grades_creditingform_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Partial_Grades')
            and   name  = 'users_grades_FK'
            and   indid > 0
            and   indid < 255)
   drop index Partial_Grades.users_grades_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Partial_Grades')
            and   name  = 'subjects_grades_FK'
            and   indid > 0
            and   indid < 255)
   drop index Partial_Grades.subjects_grades_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Partial_Grades')
            and   type = 'U')
   drop table Partial_Grades
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Roles')
            and   type = 'U')
   drop table Roles
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Subjects')
            and   name  = 'subjects_users_FK'
            and   indid > 0
            and   indid < 255)
   drop index Subjects.subjects_users_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Subjects')
            and   type = 'U')
   drop table Subjects
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Users')
            and   name  = 'groups_users_FK'
            and   indid > 0
            and   indid < 255)
   drop index Users.groups_users_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Users')
            and   name  = 'user_roles_FK'
            and   indid > 0
            and   indid < 255)
   drop index Users.user_roles_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Users')
            and   type = 'U')
   drop table Users
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('subjects_groups')
            and   name  = 'subjects_groups2_FK'
            and   indid > 0
            and   indid < 255)
   drop index subjects_groups.subjects_groups2_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('subjects_groups')
            and   name  = 'subjects_groups_FK'
            and   indid > 0
            and   indid < 255)
   drop index subjects_groups.subjects_groups_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('subjects_groups')
            and   type = 'U')
   drop table subjects_groups
go

/*==============================================================*/
/* Table: Crediting_Form                                        */
/*==============================================================*/
create table Crediting_Form (
   id_crediting_form    numeric              identity,
   name                 varchar(100)         not null,
   constraint PK_CREDITING_FORM primary key nonclustered (id_crediting_form)
)
go

/*==============================================================*/
/* Table: Final_Grades                                          */
/*==============================================================*/
create table Final_Grades (
   id_final_grade       numeric              identity,
   id_crediting_form    numeric              not null,
   id_user              numeric              null,
   id_subject           numeric              not null,
   grade                decimal(2,1)         not null,
   date                 datetime             not null,
   comment              text                 null,
   constraint PK_FINAL_GRADES primary key nonclustered (id_final_grade)
)
go

/*==============================================================*/
/* Index: subjects_finalgrades_FK                               */
/*==============================================================*/
create index subjects_finalgrades_FK on Final_Grades (
id_subject ASC
)
go

/*==============================================================*/
/* Index: finalgrades_creditingform_FK                          */
/*==============================================================*/
create index finalgrades_creditingform_FK on Final_Grades (
id_crediting_form ASC
)
go

/*==============================================================*/
/* Index: users_finalgrades_FK                                  */
/*==============================================================*/
create index users_finalgrades_FK on Final_Grades (
id_user ASC
)
go

/*==============================================================*/
/* Table: Groups                                                */
/*==============================================================*/
create table Groups (
   id_group             numeric              identity,
   group_name           varchar(50)          not null,
   numeric_group_name   varchar(50)          not null,
   constraint PK_GROUPS primary key nonclustered (id_group)
)
go

/*==============================================================*/
/* Table: Partial_Grades                                        */
/*==============================================================*/
create table Partial_Grades (
   id_grade             numeric              identity,
   id_user              numeric              not null,
   id_crediting_form    numeric              not null,
   id_subject           numeric              not null,
   grade                decimal(2,1)         not null,
   date                 datetime             not null,
   comment              text                 null,
   constraint PK_PARTIAL_GRADES primary key nonclustered (id_grade)
)
go

/*==============================================================*/
/* Index: subjects_grades_FK                                    */
/*==============================================================*/
create index subjects_grades_FK on Partial_Grades (
id_subject ASC
)
go

/*==============================================================*/
/* Index: users_grades_FK                                       */
/*==============================================================*/
create index users_grades_FK on Partial_Grades (
id_user ASC
)
go

/*==============================================================*/
/* Index: grades_creditingform_FK                               */
/*==============================================================*/
create index grades_creditingform_FK on Partial_Grades (
id_crediting_form ASC
)
go

/*==============================================================*/
/* Table: Roles                                                 */
/*==============================================================*/
create table Roles (
   id_role              numeric              identity,
   role_name            varchar(50)          null,
   constraint PK_ROLES primary key nonclustered (id_role)
)
go

/*==============================================================*/
/* Table: Subjects                                              */
/*==============================================================*/
create table Subjects (
   id_subject           numeric              identity,
   id_user              numeric              not null,
   subject_name         varchar(100)         not null,
   constraint PK_SUBJECTS primary key nonclustered (id_subject)
)
go

/*==============================================================*/
/* Index: subjects_users_FK                                     */
/*==============================================================*/
create index subjects_users_FK on Subjects (
id_user ASC
)
go

/*==============================================================*/
/* Table: Users                                                 */
/*==============================================================*/
create table Users (
   id_user              numeric              identity,
   id_role              numeric              not null,
   id_group             numeric              null,
   login                varchar(25)          not null,
   password             varchar(255)         not null,
   first_name           varchar(30)          not null,
   last_name            varchar(30)          not null,
   email                varchar(30)          not null,
   status               bit                  not null,
   album_number         numeric              null,
   constraint PK_USERS primary key nonclustered (id_user)
)
go

/*==============================================================*/
/* Index: user_roles_FK                                         */
/*==============================================================*/
create index user_roles_FK on Users (
id_role ASC
)
go

/*==============================================================*/
/* Index: groups_users_FK                                       */
/*==============================================================*/
create index groups_users_FK on Users (
id_group ASC
)
go

/*==============================================================*/
/* Table: subjects_groups                                       */
/*==============================================================*/
create table subjects_groups (
   id_subject           numeric              not null,
   id_group             numeric              not null,
   constraint PK_SUBJECTS_GROUPS primary key (id_subject, id_group)
)
go

/*==============================================================*/
/* Index: subjects_groups_FK                                    */
/*==============================================================*/
create index subjects_groups_FK on subjects_groups (
id_subject ASC
)
go

/*==============================================================*/
/* Index: subjects_groups2_FK                                   */
/*==============================================================*/
create index subjects_groups2_FK on subjects_groups (
id_group ASC
)
go

alter table Final_Grades
   add constraint FK_FINAL_GR_FINALGRAD_CREDITIN foreign key (id_crediting_form)
      references Crediting_Form (id_crediting_form)
go

alter table Final_Grades
   add constraint FK_FINAL_GR_SUBJECTS__SUBJECTS foreign key (id_subject)
      references Subjects (id_subject)
go

alter table Final_Grades
   add constraint FK_FINAL_GR_USERS_FIN_USERS foreign key (id_user)
      references Users (id_user)
go

alter table Partial_Grades
   add constraint FK_PARTIAL__GRADES_CR_CREDITIN foreign key (id_crediting_form)
      references Crediting_Form (id_crediting_form)
go

alter table Partial_Grades
   add constraint FK_PARTIAL__SUBJECTS__SUBJECTS foreign key (id_subject)
      references Subjects (id_subject)
go

alter table Partial_Grades
   add constraint FK_PARTIAL__USERS_GRA_USERS foreign key (id_user)
      references Users (id_user)
go

alter table Subjects
   add constraint FK_SUBJECTS_SUBJECTS__USERS foreign key (id_user)
      references Users (id_user)
go

alter table Users
   add constraint FK_USERS_GROUPS_US_GROUPS foreign key (id_group)
      references Groups (id_group)
go

alter table Users
   add constraint FK_USERS_USER_ROLE_ROLES foreign key (id_role)
      references Roles (id_role)
go

alter table subjects_groups
   add constraint FK_SUBJECTS_SUBJECTS__SUBJECTS foreign key (id_subject)
      references Subjects (id_subject)
go

alter table subjects_groups
   add constraint FK_SUBJECTS_SUBJECTS__GROUPS foreign key (id_group)
      references Groups (id_group)
go

